package com.mxfcode.bbs_common.util;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/***
 * 批量插入工具类
 */
@Service
public class JdbcTemplateHelp {
    @Autowired
    private JdbcTemplate jdbcTemplate;
//    @Autowired
//    private CommonDataService commonDataService;

//    /***
//     * 批量插入
//     * @param tableName
//     * @param dataList
//     */
//    public void batchInsertTable(String tableName, List<Map<String, Object>> dataList) {
//        StringBuffer sbf = new StringBuffer();
//        StringBuffer valsbf = new StringBuffer();
//        sbf.append(" insert into ");
//        sbf.append(tableName + " (");
//        valsbf.append("(");
//        //insert into stock(id,code,name) value(?,?,?)
//        List<Map<String, Object>> fieldList = commonDataService.getTableField(tableName);
//        for (Map<String, Object> fieldMap : fieldList) {
//            String field_name = fieldMap.get("field_name").toString();
//            sbf.append(field_name);
//            sbf.append(",");
//
//            //对应一个valuer
//            System.out.println(fieldMap.get("content") + "-------" + fieldMap.get("show_type") + "-------" + fieldMap.get("type"));
//            if (DbTypeUtil.isOracle()) {
//                if ("Date".equals(fieldMap.get("type"))) {
//                    if ("datetime".equals(fieldMap.get("show_type"))) {
//                        valsbf.append("to_date(?,'yyyy-mm-dd hh24:mi:ss'),");
//                    } else {
//                        valsbf.append("to_date(?,'yyyy-mm-dd'),");
//                    }
//                } else {
//                    valsbf.append("?,");
//                }
//            } else {
//                valsbf.append("?,");
//            }
//        }
//        String sqlTmp = sbf.toString();
//        String sqlVal = valsbf.toString();
//        if (sqlTmp.endsWith(",")) {
//            sqlTmp = sqlTmp.substring(0, sqlTmp.length() - 1);
//            sqlVal = sqlVal.substring(0, sqlVal.length() - 1);
//        }
//        sbf = new StringBuffer();
//        sbf.append(sqlTmp);
//        if (DbTypeUtil.isOracle()) {
//            sbf.append(") values ");
//        } else {
//            sbf.append(") value ");
//        }
//        sbf.append(sqlVal);
//        sbf.append(")");
//        jdbcTemplate.batchUpdate(sbf.toString(), new BatchPreparedStatementSetter() {
//            @Override
//            public void setValues(PreparedStatement ps, int i) throws SQLException {
//                for (int j = 0; j < fieldList.size(); j++) {
//                    Map<String, Object> fieldMap = fieldList.get(j);
//                    String field_name = fieldMap.get("field_name").toString();
//                    //ps.setObject(j+1,dataList.get(i).get(field_name));
//                    //如若有默认值不管
//                    if (AOSUtils.isNotEmpty(fieldMap.get("field_default")) && AOSUtils.isEmpty(dataList.get(i).get(field_name))) {
//                        ps.setObject(j + 1, fieldMap.get("field_default").toString());
//                    } else {
//                        ps.setObject(j + 1, dataList.get(i).get(field_name));
//                    }
//                }
//            }
//
//            @Override
//            public int getBatchSize() {
//                return dataList.size();
//            }
//        });
//    }
    /**
     * 批量插入 注意:dataList里面只存放要更新的字段和id 不能一股脑插入不用update的字段 例如:create_by 只支持id字段为id的表
     * author:zcy
     * time:2019-7-19
     *
     * @param tableName
     * @param dataList
     */
    public void batchUpdateTableByID(String tableName, List<Map<String, Object>> dataList) {
        StringBuffer updateSql = new StringBuffer(" update " + tableName + " set ");
        Map<String, Object> standardMap = dataList.get(0);
        List<String> keyArray = new ArrayList<>();
        standardMap.forEach((k, v) -> {
            String keyStr = k;
            if (!keyStr.equals("ID")) {
                keyArray.add(k);
                updateSql.append(k + "=?, ");
            }
        });
        keyArray.add("ID");
        String updateSqlFinal = updateSql.substring(0, updateSql.lastIndexOf(","));
        updateSqlFinal = updateSqlFinal.concat(" where ID=?");
        System.out.println(updateSqlFinal);
        jdbcTemplate.batchUpdate(updateSqlFinal, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                for (String key : keyArray) {
                    ps.setObject(keyArray.indexOf(key) + 1, dataList.get(i).get(key));
                }
            }

            @Override
            public int getBatchSize() {
                return dataList.size();
            }
        });

    }
    /**
     * 批量插入 注意:dataList里面只存放要更新的字段和id 不能一股脑插入不用update的字段 例如:create_by 只支持id字段为id的表
     * author:zcy
     * time:2019-7-19
     *
     * @param tableName
     * @param dataList
     */
    public void batchUpdateTable(String tableName, List<Map<String, Object>> dataList) {
        StringBuffer updateSql = new StringBuffer(" update " + tableName + " set ");
        Map<String, Object> standardMap = dataList.get(0);
        List<String> keyArray = new ArrayList<>();
        standardMap.forEach((k, v) -> {
            String keyStr = k;
            if (!keyStr.equals("id")) {
                keyArray.add(k);
                updateSql.append(k + "=?, ");
            }
        });
        keyArray.add("id");
        String updateSqlFinal = updateSql.substring(0, updateSql.lastIndexOf(","));
        updateSqlFinal = updateSqlFinal.concat(" where id=?");
        System.out.println(updateSqlFinal);
        jdbcTemplate.batchUpdate(updateSqlFinal, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                for (String key : keyArray) {
                    ps.setObject(keyArray.indexOf(key) + 1, dataList.get(i).get(key));
                }
            }

            @Override
            public int getBatchSize() {
                return dataList.size();
            }
        });

    }
}
